from google.appengine.ext import webapp
from google.appengine.ext.webapp import util
import datetime
from django.utils import simplejson
#from ft_utils import *
from ft_table import FtTable
import logging
from sql.sqlbuilder import SQL
import dlz_config as dc




def get_request(r):
    fields = 'client_name project_name dir from_dt limit project_number sort start tableid testing_type to_dt PARENT_RECORD_ID'.split()
    request = {}
    for fld in fields:
        request[fld] = r.get(fld)
    tableid = request['tableid']
    if tableid:
        pass
    else:
        request['tableid'] = int(dc.get('DLZ_COMPACTION_JOIN')['ft_table_id'])
    
    where_clause = []
    if request['to_dt']:
        where_clause.append( "form_date <= '%s'" % request['to_dt'])
    if request['PARENT_RECORD_ID']:
        where_clause.append( "PARENT_RECORD_ID = '%s'" % request['PARENT_RECORD_ID'])
    if request['from_dt']:
        where_clause.append( "form_date >= '%s'" % request['from_dt'])
    if request['client_name'] and request['client_name']!='All':
        where_clause.append( "client_name matches '%s'" % request['client_name'])
    if request['project_number'] and request['client_name']!='All':
        #if project number is available then delete client_name
        where_clause.append( "project_number = '%s'" % request['project_number'])
        for item in where_clause:
            if item.startswith('client_name'):
                where_clause.remove(item)
        
    
    if len(where_clause)==0:
        request['where'] = ''
    else:
        request['where'] = ' where %s ' % ' AND '.join(where_clause)
    return request

class TestingHandler(webapp.RequestHandler):
    '''Get data from the testing tables, compaction or asphalt'''
    def get(self):
        self.post()
    def getFields(self, tableid):
        '''return the schema of a table'''
        ft_client = FtTable(None, None, tableid)
        #ft_client = getFtClient(USERNAME, PASSWORD)
        response = ft_client.SqlQuery(SQL().describeTable(tableid))#processResponse(ft_client.query(SQL().describeTable(tableid)))
        results = []
        TYPES = {'string': 'String', 'number': 'Float', 'datetime':'date', 'location': 'String'}
        for r in response:
            if True: #r['name'][0].islower():
                results.append({'name': r['name'], 
                                'type': TYPES[r['type']],
                                'header': r['name'],
                                'dataIndex': r['name']})
        return results

    def getMeta(self, tableid, sortinfo=None, paging=None):
        '''return the meta data of a table for extjs data store'''
        fields =  self.getFields(tableid)
        if sortinfo:
            pass
        else:
            sortinfo = {
                       "field": fields[0]['name'],
                       "direction": "ASC"
                    }
        paging = {'start': 0, 'limit': 10}
        return {
                    "idProperty": "ID",
                    "root": "rows",
                    "totalProperty": "total",
                    "successProperty": "success",
                    "fields":fields,
                    # used by store to set its sortInfo
                    "sortInfo":sortinfo,
                    # paging data (if applicable)
                    "start": paging['start'],
                    "limit": paging['limit'],
                    'groupField': 'project_name'
                    
                    # custom property
                    #"foo": "bar"
                }
    def getRecords(self, options):
        '''query a table and return a list of records'''
        #dummy records
        tableid = options['tableid']
        ft_client = FtTable(None, None, tableid)#getFtClient(USERNAME, PASSWORD)
        
        sql1 = 'select * from %(tableid)s  %(where)s ORDER BY %(sort)s  %(dir)s ' % options
        if options['start']:
            sql1 = sql1 + ' OFFSET %(start)s '  % options
        if options['limit']:
            sql1 = sql1 + ' LIMIT %(limit)s ' % options 
            #response = ft_client.SqlQuery('select * from %s  where %s ORDER BY %s  %s OFFSET %s LIMIT %s ' % (tableid, options['where'], sort_fld, sort_dir, options['start'], options['limit']))
        sql2 = 'select COUNT() as count from %(tableid)s %(where)s' % options
            
            
#            response = ft_client.SqlQuery('select * from %s OFFSET %s LIMIT %s' % (tableid, options['start'], options['limit']))
#            rs = ft_client.SqlQuery('select COUNT() as count from %s' % tableid)
#            if len(rs)==0:
#                count = 0
#            else:
#                count = rs[0]['count']
        response = ft_client.SqlQuery(sql1)
        rs = ft_client.SqlQuery(sql2)
        if len(rs)==0:
            count = 0
        else:
            count = rs[0]['count']
        return response, count
    
    def post(self):
        
        request = get_request(self.request)
        sortInfo = {'field':request['sort'], 'direction': request['dir']}
        paging = {'start': request['start'], 'limit': request['limit']}
        meta = self.getMeta(request['tableid'], sortInfo, paging)
        
        
        
        format = self.request.get('format')
#        if format=='csv':
#            #download in csv format, ignore the start and limit
#            request['start'] = ''
#            request['limit'] = ''
#            records = self.getRecords(request)
#            csv_text = processCompaction(records[0])
#            self.response.headers["Content-Type"] = "application/vnd.ms-excel"
#            self.response.headers["Content-Disposition"] = "attachment;Filename=compaction.csv"   
#            self.response.out.write(csv_text)
        
        records = self.getRecords(request)
        for record in records[0]:
            for fld in 'wet_unit_weight    moisture_content    dry_unit_weight    moisture_contentlab    dry_unit_weight_lab    max_dry_unit_weight    optimum_moisture_content    specified    obtained'.split():
                if record.get(fld):
                    
                    try:
                        record[fld] = '%.1f' % float(record[fld])
                    except:
                        pass
                    #logging.info('%s:%s' % (fld, record[fld]))
                
                
                
        total = records[1]
        results = {'metaData': meta, 'success': True, 'total': total, 'rows': records[0]}
        self.response.headers["Content-Type"] = "application/json"   
        self.response.out.write(simplejson.dumps(results))
        
#        
#    def post(self):
#        #a list of tables related to project table
#        self.getData()
#        return
#        tableid = self.request.get('tableid')
#        limit = self.request.get('limit')
#        start = self.request.get('start')
#        sort_fld = self.request.get('sort')
#        sort_dir = self.request.get('dir')
#        parentid = None
#        prefix = None
#        try:
#            tableid = int(tableid)
#        except:
#            prefix, tableid, parentid = tableid.split('--')
#            tableid = int(tableid)
#        
#        if prefix=='query':
#            testing_type = self.request.get('testing_type')
#            tableid = DLZ_COMPACTION
#            if testing_type=='compaction':
#                from_dt = self.request.get('from_dt')
#                to_dt = self.request.get('to_dt')
#                client_name = self.request.get('client_name')
#                project_number = self.request.get('project_number')
#                where_clause = ["date_<='%s'" % to_dt, "date_>='%s'" % from_dt]
#                if client_name and client_name!='All':
#                    where_clause.append( "client_name = '%s'" % client_name)
#                if project_number and client_name!='All':
#                    where_clause.append( "project_number = '%s'" % project_number)
#                rows = self.getRecords(tableid, {'sort': sort_fld, 'dir': sort_dir, 'limit': limit, 'start': start, 'where': ' AND '.join(where_clause)})
#                sortinfo = {
#                       "field": sort_fld,
#                       "direction": sort_dir
#                    }
#                meta = self.getMeta(tableid)
#                total = rows[1]
#                results = {'metaData': meta, 'success': True, 'total': total, 'rows': rows[0]}
#                self.response.out.write(simplejson.dumps(results))
#                return
#        
#        
#        sortInfo = {'field': sort_fld, 'direction': sort_dir}
#        meta = self.getMeta(tableid, sortInfo)
#        if parentid:
#            rows = self.getRecords(tableid, {'sort': sort_fld,'dir': sort_dir,'limit': limit, 'start': start, 'where': 'PARENT_RECORD_ID=%s' % parentid})
#        else:
#            rows = self.getRecords(tableid, {'sort': sort_fld,'dir': sort_dir,'limit': limit, 'start': start})
#        total = rows[1]
#        results = {'metaData': meta, 'success': True, 'total': total, 'rows': rows[0]}
#        self.response.out.write(simplejson.dumps(results))
        